from werkzeug.security import generate_password_hash, check_password_hash
from app_db import db, UserMixin, login_manager


# 加载用户函数
@login_manager.user_loader
def load_user(user_id):
    return User.query.filter_by(id=int(user_id), is_valid=1).first()


# 角色
class Role(db.Model):
    __tablename__ = 'role'
    id = db.Column(db.SmallInteger, primary_key=True, comment='主键id')
    name = db.Column(db.String(64), nullable=False, comment='角色名')
    default = db.Column(db.Boolean, default=False, index=True, comment='只能有一个角色为True， 否则为False')
    permissions = db.Column(db.Integer)
    is_valid = db.Column(db.Boolean, default=True,comment='是否有效：1：有效')

    users = db.relationship('User', backref='role', lazy='dynamic')

    def __init__(self, **kwargs):
        super(Role, self).__init__(**kwargs)
        if self.permissions is None:
            self.permissions = 0


# 用户表
class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键ID')
    email = db.Column(db.String(64), nullable=False, comment='邮箱')
    name = db.Column(db.String(10), nullable=False, comment='真实姓名')
    password_hash = db.Column(db.String(128), nullable=False, comment='登陆密码')
    registered_on = db.Column(db.DateTime, nullable=False, comment='注册时间戳')
    # confirmed = db.Column(db.Boolean, nullable=False, default=False, comment='是否邮箱验证')
    # confirmed_on = db.Column(db.DateTime, nullable=True, comment='邮箱验证时间')
    role_id = db.Column(db.SmallInteger, db.ForeignKey('role.id'), comment='角色id')
    is_valid = db.Column(db.Boolean, default=True, comment='是否有效')

    def password(self, password=None):
        if password:
            self.password_hash = generate_password_hash(password)
        else:
            raise AttributeError('请输入密码!')

    def verify_password(self, passowrd):
        return check_password_hash(self.password_hash, passowrd)

    def get_id(self):
        return self.id

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def can(self, permission):
        return self.role_id is not None and self.role_id == permission

# 产品元数据
class DimProduct(db.Model):
    __tablename__ = 'dim_product'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    product_name = db.Column(db.String(30), unique=True, comment='产品名称')
    product_describe = db.Column(db.String(100), comment='产品描述')
    owner_id = db.Column(db.Integer, comment='产品所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    db_id = db.relationship('DimDb', backref='dim_product', lazy='dynamic')

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'product_name: {}'.format(self.product_name)
        return info


# 标准字段分类类型
class DimColumnCategory(db.Model):
    __tablename__ = 'dim_column_category'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    category_name = db.Column(db.String(30), unique=True, comment='分类名称')
    owner_id = db.Column(db.Integer, comment='分类所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    dim_column_id = db.relationship('DimColumn', backref='dim_column_category', lazy='dynamic')

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'category_name: {}'.format(self.category_name)
        return info


# 标准字段
class DimColumn(db.Model):
    __tablename__ = 'dim_column'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    column_name = db.Column(db.String(255),  nullable=False, comment='属性名')
    data_type_id = db.Column(db.Integer,  db.ForeignKey('dim_data_type.id'), nullable=False,
                             comment='属性数据类型id')
    data_length = db.Column(db.Integer, comment='属性数据类型长度')
    column_describe = db.Column(db.String(255), comment='属性描述')
    category_id = db.Column(db.Integer, db.ForeignKey('dim_column_category.id'), nullable=False,
                            comment='标准字段分类')
    type_id = db.Column(db.SmallInteger, nullable=False, comment='1： 维度； 2：度量')

    is_null = db.Column(db.SmallInteger, nullable=False, default=True, comment='是否可空：True： 是； False：否')
    is_key = db.Column(db.SmallInteger, nullable=False, default=False, comment='是否主键：True： 是； False：否')
    defalut_value = db.Column(db.String(200), comment='默认值')

    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    __table_args__ = (
            db.UniqueConstraint('column_name', 'data_type_id', name='uix_column_name_data_type_id'),
        )


# 数据类型元数据
class DimDataType(db.Model):
    __tablename__ = 'dim_data_type'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    data_type_name = db.Column(db.String(30), nullable=False, comment='数据类型')
    data_type_describe = db.Column(db.String(100), comment='数据类型描述')
    db_driver_id = db.Column(db.Integer, db.ForeignKey('dim_db_driver_type.id'), nullable=False, comment='数据库驱动类型')
    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    dim_column_id_ref = db.relationship('DimColumn', backref='dim_data_type', lazy='dynamic')

    __table_args__ = (
        db.UniqueConstraint('data_type_name', 'db_driver_id', name='uix_db_drive_data_type_name'),
    )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'data_type_name: {}'.format(self.data_type_name)
        return info


# 数据库驱动类型元数据
class DimDbDriverType(db.Model):
    __tablename__ = 'dim_db_driver_type'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    db_driver_name = db.Column(db.String(30), unique=True, comment='数据库驱动类型')
    db_driver_describe = db.Column(db.String(50), comment='数据库驱动描述')
    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    db_name = db.relationship('DimDb', backref='dim_db_driver_type', lazy='dynamic')
    db_driver_id_ref = db.relationship('DimDataType', backref='dim_db_driver_type', lazy='dynamic')

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'db_driver_name: {}'.format(self.db_driver_name)
        return info


# 数据库维表
class DimDb(db.Model):
    __tablename__ = 'dim_db'
    # 初始化字段，注意，一定要有pk
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键id')
    db_name = db.Column(db.String(100),  nullable=False, comment='数据库编码')
    db_describe = db.Column(db.String(100), comment='数据库描述')
    db_driver_id = db.Column(db.Integer, db.ForeignKey('dim_db_driver_type.id'),  nullable=False,
                             comment='数据库驱动类型id')
    product_id = db.Column(db.Integer, db.ForeignKey('dim_product.id'),  nullable=False, comment='产品id')
    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    table_name = db.relationship('DbTableRelate', backref='dim_db', lazy='dynamic')
    column_name = db.relationship('TableColumnRelate', backref='dim_db', lazy='dynamic')

    __table_args__ = (
            db.UniqueConstraint('db_name', 'db_driver_id', name='uix_db_db_driver_id'),
        )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'db_name: {}, db_describe: {}'.format(self.db_name, self.db_describe)
        return info


# 数据库与表对应关系
class DbTableRelate(db.Model):
    __tablename__ = 'db_table_relate'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主健表id')
    db_id = db.Column(db.Integer, db.ForeignKey('dim_db.id'), nullable=False, comment='数据库id')
    table_name = db.Column(db.String(500), nullable=False, comment='表名')
    table_describe = db.Column(db.String(1000), comment='表描述')
    table_info = db.Column(db.JSON, comment='表信息')
    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')
    column_name = db.relationship('TableColumnRelate', backref='db_table_relate', lazy='dynamic')

    __table_args__ = (
            db.UniqueConstraint('db_id', 'table_name', name='uix_db_table_name'),
        )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'table_name: {}, table_describe: {}'.format(self.table_name, self.table_describe)
        return info


# 表与字段对应关系对应关系
class TableColumnRelate(db.Model):
    __tablename__ = 'table_column_relate'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键属性id')
    db_id = db.Column(db.Integer, db.ForeignKey('dim_db.id'), nullable=False, comment='数据库id')
    table_id = db.Column(db.Integer, db.ForeignKey('db_table_relate.id'), nullable=False, comment='表id')
    column_id = db.Column(db.Integer,  nullable=False, comment='属性名id')
    is_valid = db.Column(db.Boolean, default=True, comment='true:有效；false:无效')

    __table_args__ = (
            db.UniqueConstraint('table_id', 'column_id', name='uix_table_column_id'),
        )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'table_id: {}, column_id: {}'.format(self.table_id, self.column_id)
        return info


# create table sql
class TableSQL(db.Model):
    __tablename__ = 'table_sql'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键属性id')
    db_id = db.Column(db.Integer, db.ForeignKey('dim_db.id'), nullable=False, comment='数据库id')
    table_id = db.Column(db.Integer, db.ForeignKey('db_table_relate.id'), nullable=False, comment='表id')
    sql = db.Column(db.Text,  nullable=False, comment='建表SQL')
    owner_id = db.Column(db.Integer, comment='所有者id')

    __table_args__ = (
            db.UniqueConstraint('db_id', 'table_id', name='uix_db_table_id'),
        )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'table_id: {}, column_id: {}'.format(self.table_id, self.column_id)
        return info


# mapping info
class MappingInfo(db.Model):
    __tablename__ = 'mapping_info'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键属性id')
    db_id = db.Column(db.Integer, nullable=False, comment='数据库id')
    table_id = db.Column(db.Integer, nullable=False, comment='表id')
    column_id = db.Column(db.Integer, nullable=False, comment='列id')

    target_db = db.Column(db.String(100), comment='目标数据库')
    target_table = db.Column(db.String(500), comment='目标表')
    target_column = db.Column(db.String(255), comment='目标属性')

    owner_id = db.Column(db.Integer, comment='所有者id')
    is_valid = db.Column(db.Boolean, default=True, comment='是否有效')

    __table_args__ = (
            db.UniqueConstraint('db_id', 'table_id', 'column_id', 'target_column',
                                name='uix_db_table_column_tar_id'),
        )

    def __repr__(self):
        # 为了get时返回结果，并重组成json格式
        info = 'table_id: {}, column_id: {}'.format(self.table_id, self.column_id)
        return info


# sqoop info
class SqoopInfo(db.Model):
    __tablename__ = 'sqoop_info'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键属性id')
    source_db = db.Column(db.String(50), nullable=False, comment='来源db')
    source_table = db.Column(db.String(100), nullable=False, comment='来源表')
    target_info = db.Column(db.String(1000), nullable=False, comment='目标库表/存储路径')
    sqoop_cmd = db.Column(db.TEXT, nullable=False, comment='sqoop命令')
    owner_id = db.Column(db.Integer, nullable=False, comment='所有者')

    __table_args__ = (
        db.UniqueConstraint('source_db', 'source_table', name='uix_sqoop_source_db_table'),
    )
